{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2bde18cd-7887-499f-9611-f64af5ee5790",
   "metadata": {},
   "source": [
    "# 营收预估案例"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a33e7ca0-5596-4c18-88d2-52d808cfda6e",
   "metadata": {},
   "source": [
    "## 前置说明"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1a7788b-df37-4e48-9599-b2aed0b4c6dd",
   "metadata": {},
   "source": [
    "### 环境说明"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd8ef490-f67d-485a-930f-bd07a9e236fa",
   "metadata": {},
   "source": [
    "conda版本：https://mirrors.tuna.tsinghua.edu.cn/anaconda/archive/Anaconda3-2021.11-Windows-x86_64.exe\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e5b0642-63be-4c0b-879a-3a55e6f53417",
   "metadata": {},
   "source": [
    "python核心包：\n",
    "- numpy_financial==1.0.0\n",
    "- xlsxwriter==3.0.1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1db1fb7e-9d61-4435-ae7c-090b5920e919",
   "metadata": {},
   "source": [
    "### 业务说明"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5942df1c-8785-48ab-8fb3-2f9f11dc447f",
   "metadata": {},
   "source": [
    "老板有未来几年公司计划的营收目标，想让你拆解成月度目标，自然想到：\n",
    "- 下月营收 = 上月营收 * （1 + 增速）\n",
    "- 每年的总目标不同，年间的增速可以不同，年内可以相同"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24f91967-8009-4a32-928e-60fabdb3afcb",
   "metadata": {},
   "source": [
    "多说两句：\n",
    "- 强调这只是众多预估方法中的一种，这种方法最平滑\n",
    "- 强调教学只是展示最小可实现功能的逻辑，实际情况大家根据业务需要去修改"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33ab3169-929b-4c03-af5d-e4090eb2b83c",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 参数设置"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "05972205-ea00-4e5a-9fd7-bb07c96153b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 当前每月营收\n",
    "current_month_list = [\n",
    "    ['2020-01', 5000],\n",
    "    ['2020-02', 5500],\n",
    "    ['2020-03', 5200],\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "47752b45-b2a4-4b54-a9bb-c8fe010dcb76",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 目标全年营收\n",
    "target_year_list = [\n",
    "    ['2020', 80000],\n",
    "    ['2021', 120000],\n",
    "    ['2022', 150000],\n",
    "]\n",
    "target_year_dict = {k:v for k,v in target_year_list}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "77e01633-af55-4263-963f-d0e23d3c3291",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'2020': 80000, '2021': 120000, '2022': 150000}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "target_year_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a0446894-140a-4ee5-86f9-914152289e47",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 展示的开始结束时间\n",
    "start_date = '2020-01'\n",
    "end_date = '2022-12'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01282552-9c03-4948-9982-f6cf69eec05a",
   "metadata": {},
   "source": [
    "## 计算目标年份的增长率"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2252a560-b928-4ed7-9bc4-ae38057ab4c3",
   "metadata": {},
   "source": [
    "### 计算当年剩余"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "701739a2-8ed9-48a6-ae80-3a11df6c0691",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5227575a-7fa3-4005-af3e-abf4843878bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "current_month_df = pd.DataFrame(current_month_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "52396f07-5e09-4713-b0db-f344b73c78a2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>5500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>5200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     month  revenue\n",
       "0  2020-01     5000\n",
       "1  2020-02     5500\n",
       "2  2020-03     5200"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "current_month_df.columns = ['month', 'revenue']\n",
    "current_month_df = current_month_df.sort_values(by='month')\n",
    "current_month_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f84a0d7c-d951-4597-a54f-f9e091855446",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>revenue</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>5000</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>5500</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>5200</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     month  revenue  year\n",
       "0  2020-01     5000  2020\n",
       "1  2020-02     5500  2020\n",
       "2  2020-03     5200  2020"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 生成年份\n",
    "current_month_df['year'] = current_month_df['month'].apply(lambda x: x[:4])\n",
    "current_month_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "56e293b8-cb95-4cca-8b5c-d2303ac026c6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>3</td>\n",
       "      <td>15700</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      month  revenue\n",
       "year                \n",
       "2020      3    15700"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 计算当前真实数据中的全年发生，这里计数、金额合计都需要\n",
    "current_group_df = current_month_df.groupby('year').agg({'month': 'count', 'revenue': 'sum'})\n",
    "current_group_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d580a2fe-d45c-4d35-8a21-c8c2e0830913",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('2020', 80000, 64300, 3, 9)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 当前年份\n",
    "this_year = current_group_df.index[0]\n",
    "# 获取当前年份的目标\n",
    "this_year_target = target_year_dict[this_year]\n",
    "# 当年剩余营收目标\n",
    "this_year_residual = this_year_target - current_group_df['revenue'][0]\n",
    "# 当前剩余月份\n",
    "this_year_month_past = current_group_df['month'][0]\n",
    "this_year_month_residual = 12 - this_year_month_past\n",
    "# 查看结果\n",
    "this_year, this_year_target, this_year_residual, this_year_month_past, this_year_month_residual"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "bd9ad573-f4e4-44e1-b027-4fd83b342746",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5200"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 假设增速年内每月固定增长r，真实的末月为x，预估的首月为x*(1+r)，次月为x*(1+r)**2... 累计所有和全年剩余目标接近即可\n",
    "this_year_actual_last = current_month_df[current_month_df['year'] == this_year]['revenue'].iloc[-1]\n",
    "this_year_actual_last"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4917a3fe-2514-4c98-9172-8070538b7b6b",
   "metadata": {},
   "source": [
    "### 计算等比数列r"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9798afa-9d09-4fe1-a5c9-5c5bf1934148",
   "metadata": {},
   "source": [
    "$$f(r) = Revenue*(1+r) +  Revenue*(1+r) ^2  + \\dots +  Revenue*(1+r) ^9 = Residual\\_value$$"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c9e1f08-0c16-4293-aac4-6b5f1769a1cb",
   "metadata": {},
   "source": [
    "$if: r_1 < r_2, f(r_1) < Residual\\_value, f(r_2) > Residual\\_value，则真正的r \\in (r_1, r_2)$"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "645bb5dc-48be-49ba-9300-8f5d1fcb32ff",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 法1：暴力精度"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "c862944c-d83d-40fb-9f84-2aa0bc7339a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 假设比例在 0.1~1.0 之间，每次移动 0.1\n",
    "# 这里用range报错，所以才用np.arange\n",
    "this_year_r_pred_list = np.arange(0.1, 1.1, 0.1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "442c591b-1c4e-4748-8b52-47a993c26763",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5200"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "this_year_actual_last"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "c1905a2e-8199-4f1b-be3b-0ca76189256f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "this_year_month_residual"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "c97c1b44-d2ed-4e13-baad-b78e3cd64ec2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "r between 0~0.1, 0.1 total value is 77674.60792520003 more than resisual 64300\n"
     ]
    }
   ],
   "source": [
    "# 暴力循环\n",
    "last_r, last_total = 0, 0\n",
    "for r in this_year_r_pred_list:\n",
    "    # 计算今年剩余月份的总和\n",
    "    # TODO 优化，等比数列\n",
    "    tmp_total = 0\n",
    "    for i in range(this_year_month_residual):\n",
    "        tmp_total += this_year_actual_last*(1+r)**(i+1)\n",
    "    if last_total < this_year_residual and tmp_total > this_year_residual:\n",
    "        print(f'r between {last_r}~{r}, {r} total value is {tmp_total} more than resisual {this_year_residual}')\n",
    "        break\n",
    "    last_r = r\n",
    "    last_total = tmp_total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "87a2ba20-6fbc-4c78-8578-200de289cd85",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "r between 0.060000000000000005~0.06999999999999999, 0.06999999999999999 total value is 66645.52939865344 more than resisual 64300\n"
     ]
    }
   ],
   "source": [
    "# 精度加10倍\n",
    "this_year_r_pred_list = np.arange(0.01, 0.1, 0.01)\n",
    "# 暴力循环\n",
    "last_r, last_total = 0, 0\n",
    "for r in this_year_r_pred_list:\n",
    "    # 计算今年剩余月份的总和\n",
    "    # TODO 优化，等比数列\n",
    "    tmp_total = 0\n",
    "    for i in range(this_year_month_residual):\n",
    "        tmp_total += this_year_actual_last*(1+r)**(i+1)\n",
    "    if last_total < this_year_residual and tmp_total > this_year_residual:\n",
    "        print(f'r between {last_r}~{r}, {r} total value is {tmp_total} more than resisual {this_year_residual}')\n",
    "        break\n",
    "    last_r = r\n",
    "    last_total = tmp_total"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbfb0064-2104-48d6-a80a-aecf490cf383",
   "metadata": {},
   "source": [
    "##### 指定精度暴力求解"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "d612c2e4-3d0b-472d-b57d-200baec513dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 精度控制，增速保留几位小数\n",
    "r_precision = 6\n",
    "# 控制增速上限，不可能1个月做到上个月10倍吧\n",
    "r_up_limit = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "adcf8057-a417-4fbd-9fc0-6fabac00eef4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 根据精度来\n",
    "this_year_r_pred_list = np.arange(0.1**r_precision, r_up_limit, 0.1**r_precision)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "340ab671-9f41-487e-8716-80a388769303",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1.00000e-06, 2.00000e-06, 3.00000e-06, ..., 9.99997e-01,\n",
       "       9.99998e-01, 9.99999e-01])"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "this_year_r_pred_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "5044e0ba-35e1-4ec9-8b34-1f1739f165c1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "r between 0.06295800000000003~0.06295900000000003, 0.06295900000000003 total value is 64300.067043483854 more than resisual 64300\n"
     ]
    }
   ],
   "source": [
    "# 暴力循环\n",
    "last_r, last_total = 0, 0\n",
    "for r in this_year_r_pred_list:\n",
    "    # 计算今年剩余月份的总和\n",
    "    # TODO 优化，等比数列\n",
    "    tmp_total = 0\n",
    "    for i in range(this_year_month_residual):\n",
    "        tmp_total += this_year_actual_last*(1+r)**(i+1)\n",
    "    if last_total < this_year_residual and tmp_total > this_year_residual:\n",
    "        print(f'r between {last_r}~{r}, {r} total value is {tmp_total} more than resisual {this_year_residual}')\n",
    "        break\n",
    "    last_r = r\n",
    "    last_total = tmp_total"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a010ffc-4417-4966-a0b2-113d429d44b7",
   "metadata": {},
   "source": [
    "#### 法2：IRR"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ada815e-cae0-4bf7-8d9b-8a7ac264a117",
   "metadata": {},
   "source": [
    "##### 什么是NPV和IRR？"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0037b3b-68e7-41c0-b996-e6a90189ed26",
   "metadata": {},
   "source": [
    "货币是有时间价值的"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c05142b1-24f6-4d1f-9932-2267516625c8",
   "metadata": {},
   "source": [
    "代入到一个商业住宅的项目，第一年我要去建这个写字楼，之后每年收租，那么第一期的现金流CF0是一个巨额负数，也可以称之为-Investment，之后每期CF1、CF2都是正数，而折现率是我做其他事情的**机会成本**，比如如果我不做这个项目，我的钱也是可以产生价值的，我可以全部买收益率为2%无风险的国债，那么每年我都能收到国债的本息，这种过程也可以称为**货币的时间价值的体现**。由于每期我都可以投资国债，所以我每期都要用现金流除以对应期数的国债收益率。最终我将每期的现金流加和就形成了**NPV（净现值，Net Present Value）**，如果NPV大于0，那么这个项目是可以投资的，最起码不会比直接买国债赚的少。\n",
    "\n",
    "这种计算NPV的方法我们一般称为**折现现金流模型（Discounted Cash Flow，DCF）**，为了计算投资一个项目的收益率情况，我们可以先将折现率设定为一个未知数R，让R不断变动，直到让NPV等于0，此时的R就能描述出投资这个项目的平均年化收益，我们也将这个R称为**内部收益率IRR**（Internal Rate of Return），这个收益率是假设了你产生的营收可以进行再投资，因此收益率会更高"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "576a1f53-a57b-4f11-8315-73a60351622b",
   "metadata": {},
   "source": [
    "$$NPV = -Investment + \\frac{CF1}{(1+R)} + \\frac{CF2}{(1+R)^2}  + \\dots + \\frac{CFn}{(1+R)^n}$$"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d82550dd-9acb-4bf6-8b3c-4122928fe83e",
   "metadata": {},
   "source": [
    "$$0 = -Investment + \\frac{CF1}{(1+IRR)} + \\frac{CF2}{(1+IRR)^2}  + \\dots + \\frac{CFn}{(1+IRR)^n}$$"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9ed0c99-9d89-4add-bbfa-1321be71fdd8",
   "metadata": {},
   "source": [
    "$$0 = -Residual\\_value + \\frac{Revenue}{1/(1+r)} + \\frac{Revenue}{1/(1+r)^2}  + \\dots + \\frac{Revenue}{1/(1+r)^9}$$"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ba636eb-6382-4b6a-b52d-f1f17c0a95dd",
   "metadata": {},
   "source": [
    "##### 聪明的使用数学法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "f8323acc-2cd3-405e-ba24-56c103d67e96",
   "metadata": {},
   "outputs": [],
   "source": [
    "irr_in_list = [this_year_actual_last for _ in range(this_year_month_residual)]\n",
    "irr_total_list = [-this_year_residual] + irr_in_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "d1cf3a43-a034-4704-9aec-f9ca79a46f5d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[-64300, 5200, 5200, 5200, 5200, 5200, 5200, 5200, 5200, 5200]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "irr_total_list"
   ]
  },
  {
   "cell_type": "raw",
   "id": "92e04577-8d01-4e15-b5dc-c1c19a63ae1a",
   "metadata": {},
   "source": [
    "irr = np.irr(irr_total_list)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "818970ed-afc8-4282-994a-1f00332e2de5",
   "metadata": {},
   "source": [
    "pip install numpy_financial==1.0.0 -i https://pypi.douban.com/simple"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "12bd91d4-a674-49d3-b381-b77607b7a827",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy_financial as npf\n",
    "irr = npf.irr(irr_total_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "150589b0-7441-4497-bfa5-f3f3b40efd62",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.06295879494637924"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "irr2rate = 1 / (1 + irr) - 1 \n",
    "irr2rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "31fe8f97-8986-4021-8574-ee276e8515ff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(64300.000000000044, 64300)"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "irr_total = 0\n",
    "for i in range(this_year_month_residual):\n",
    "    irr_total += this_year_actual_last*(1+irr2rate)**(i+1)\n",
    "    \n",
    "irr_total, this_year_residual"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0faf7f87-5973-47b8-a7ca-c54918580146",
   "metadata": {},
   "source": [
    "## 通用计算年份增长率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "1ba7c176-f1b1-49d5-9122-59158899867b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021</td>\n",
       "      <td>120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022</td>\n",
       "      <td>150000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  revenue\n",
       "0  2020    80000\n",
       "1  2021   120000\n",
       "2  2022   150000"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "target_year_df = pd.DataFrame(target_year_list)\n",
    "target_year_df.columns = ['year', 'revenue']\n",
    "target_year_df = target_year_df.sort_values(by='year')\n",
    "target_year_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "c7e977db-7e77-439d-b8b6-86ec278847fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "target_year_df['completed'] = 0\n",
    "target_year_df['residual_month'] = 12\n",
    "target_year_df['last_month_revenue'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "698028b6-dd8f-4d4d-aee4-2c21b0f1b829",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>revenue</th>\n",
       "      <th>completed</th>\n",
       "      <th>residual_month</th>\n",
       "      <th>last_month_revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020</td>\n",
       "      <td>80000</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021</td>\n",
       "      <td>120000</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022</td>\n",
       "      <td>150000</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  revenue  completed  residual_month  last_month_revenue\n",
       "0  2020    80000          0              12                   0\n",
       "1  2021   120000          0              12                   0\n",
       "2  2022   150000          0              12                   0"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "target_year_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "e0a5f9f0-acbc-4865-b0c1-b96a9f6012d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 给已完成的年份赋值\n",
    "target_year_df = target_year_df.set_index('year')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "2914986d-bf21-491e-8f83-0b2100b85216",
   "metadata": {},
   "outputs": [],
   "source": [
    "target_year_df.loc[this_year, 'completed'] = this_year_target - this_year_residual\n",
    "target_year_df.loc[this_year, 'residual_month'] = this_year_month_residual\n",
    "target_year_df.loc[this_year, 'last_month_revenue'] = this_year_actual_last"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "c21fd76c-4305-47b5-9f1f-35c3f515107e",
   "metadata": {},
   "outputs": [],
   "source": [
    "target_year_df = target_year_df.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "f4a1da07-0cdc-4d70-a47b-699e516ae70f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>revenue</th>\n",
       "      <th>completed</th>\n",
       "      <th>residual_month</th>\n",
       "      <th>last_month_revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020</td>\n",
       "      <td>80000</td>\n",
       "      <td>15700</td>\n",
       "      <td>9</td>\n",
       "      <td>5200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021</td>\n",
       "      <td>120000</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022</td>\n",
       "      <td>150000</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  revenue  completed  residual_month  last_month_revenue\n",
       "0  2020    80000      15700               9                5200\n",
       "1  2021   120000          0              12                   0\n",
       "2  2022   150000          0              12                   0"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "target_year_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "f610b10a-0bd8-45a6-a89b-1a3370c80637",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy_financial as npf\n",
    "\n",
    "every_irr_list = []  # 存储每年计算月增速\n",
    "last_month_revenue_list = []  # 存储每年末月收入\n",
    "\n",
    "for i in target_year_df.index:\n",
    "    # 取数\n",
    "    tmp_revenue = target_year_df.loc[i, 'revenue'] \n",
    "    tmp_completed = target_year_df.loc[i, 'completed'] \n",
    "    tmp_residual_month = target_year_df.loc[i, 'residual_month'] \n",
    "    tmp_last_month_revenue = target_year_df.loc[i, 'last_month_revenue'] \n",
    "    # 获取上次最末月的值\n",
    "    if not last_month_revenue_list:  # 如果没存的话，就用df的\n",
    "        last_month_revenue_list.append(tmp_last_month_revenue)\n",
    "    tmp_last_month_revenue = last_month_revenue_list[-1]\n",
    "    # 计算irr\n",
    "    tmp_irr_in_list = [tmp_last_month_revenue for _ in range(tmp_residual_month)]\n",
    "    tmp_irr_total_list = [-(tmp_revenue - tmp_completed)] + tmp_irr_in_list\n",
    "    tmp_irr = npf.irr(tmp_irr_total_list)\n",
    "    tmp_irr2rate = 1 / (1 + tmp_irr) - 1 \n",
    "    every_irr_list.append(tmp_irr2rate)\n",
    "    # 给末月赋值用于下次迭代\n",
    "    iter_last_revenue = tmp_last_month_revenue * (1+tmp_irr2rate) ** (tmp_residual_month)\n",
    "    last_month_revenue_list.append(iter_last_revenue)  # 再存一次"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "5d516edd-14e3-4a51-9834-c8cbd3c6f6a6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "([0.06295879494637924, 0.015960789453421054, 0.020982943947220667],\n",
       " [5200, 9008.473606219515, 10893.67897466778, 13976.43526432213])"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "every_irr_list, last_month_revenue_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "70434791-6e40-42ad-a86c-e89f871a3dc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "target_year_df['mom'] = every_irr_list\n",
    "target_year_df['last_month_revenue'] = last_month_revenue_list[:-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "98ca2551-51f9-4618-b1f4-f6c6113bdfb9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>revenue</th>\n",
       "      <th>completed</th>\n",
       "      <th>residual_month</th>\n",
       "      <th>last_month_revenue</th>\n",
       "      <th>mom</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020</td>\n",
       "      <td>80000</td>\n",
       "      <td>15700</td>\n",
       "      <td>9</td>\n",
       "      <td>5200.000000</td>\n",
       "      <td>0.062959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021</td>\n",
       "      <td>120000</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>9008.473606</td>\n",
       "      <td>0.015961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022</td>\n",
       "      <td>150000</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10893.678975</td>\n",
       "      <td>0.020983</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  revenue  completed  residual_month  last_month_revenue       mom\n",
       "0  2020    80000      15700               9         5200.000000  0.062959\n",
       "1  2021   120000          0              12         9008.473606  0.015961\n",
       "2  2022   150000          0              12        10893.678975  0.020983"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "target_year_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5489d60d-f555-401d-92ae-c6d73a79dc20",
   "metadata": {},
   "source": [
    "## 对年份进行炸裂-explode"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5fb04262-8f03-489f-a128-e174b15a7008",
   "metadata": {},
   "source": [
    "### 什么是炸裂"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13aabe01-0cbb-4667-a41a-ae692e5843a5",
   "metadata": {},
   "source": [
    "当前的数据粒度不同，一个是月份、一个是年份，我们学过将月->年，那么如何将年->月呢？需要将年份炸裂！"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "c2e58b40-1d75-4d81-beaa-bbe67b880f99",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>健身时长</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>[60, 50, 50]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>[50, 40]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  姓名          健身时长\n",
       "0  a  [60, 50, 50]\n",
       "1  b      [50, 40]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df = pd.DataFrame([['a', [60, 50, 50]], ['b', [50, 40]]])\n",
    "test_df.columns = ['姓名', '健身时长']\n",
    "test_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "80a13eb6-a351-4d77-b677-58fdc7a4e303",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>健身时长</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  姓名 健身时长\n",
       "0  a   60\n",
       "0  a   50\n",
       "0  a   50\n",
       "1  b   50\n",
       "1  b   40"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df.explode('健身时长')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a66f3cfa-d8bb-4588-a65d-3cb255e67f45",
   "metadata": {},
   "source": [
    "### 对年份炸裂"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "c462263c-96c6-46f2-8b80-ed6e23c8fc8a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>revenue</th>\n",
       "      <th>completed</th>\n",
       "      <th>residual_month</th>\n",
       "      <th>last_month_revenue</th>\n",
       "      <th>mom</th>\n",
       "      <th>residual_month_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020</td>\n",
       "      <td>80000</td>\n",
       "      <td>15700</td>\n",
       "      <td>9</td>\n",
       "      <td>5200.000000</td>\n",
       "      <td>0.062959</td>\n",
       "      <td>[4, 5, 6, 7, 8, 9, 10, 11, 12]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021</td>\n",
       "      <td>120000</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>9008.473606</td>\n",
       "      <td>0.015961</td>\n",
       "      <td>[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022</td>\n",
       "      <td>150000</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10893.678975</td>\n",
       "      <td>0.020983</td>\n",
       "      <td>[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  revenue  completed  residual_month  last_month_revenue       mom  \\\n",
       "0  2020    80000      15700               9         5200.000000  0.062959   \n",
       "1  2021   120000          0              12         9008.473606  0.015961   \n",
       "2  2022   150000          0              12        10893.678975  0.020983   \n",
       "\n",
       "                       residual_month_list  \n",
       "0           [4, 5, 6, 7, 8, 9, 10, 11, 12]  \n",
       "1  [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]  \n",
       "2  [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "target_year_df['residual_month_list'] = target_year_df['residual_month'].apply(lambda x : [12-x+i+1 for i in range(x)])\n",
    "target_year_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5176b292-9356-409a-ae84-9dcace1505b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "target_month_df = target_year_df.explode('residual_month_list')\n",
    "target_month_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7947186-bdc6-4b2e-b5d3-02247bfc6bbe",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 使用累乘的方法\n",
    "mom_cumprod_se = (target_month_df['mom'] + 1).cumprod()\n",
    "mom_cumprod_se"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "60a60d8d-163c-4ed0-a864-299440e6b93c",
   "metadata": {},
   "outputs": [],
   "source": [
    "target_month_df['revenue'] = target_month_df['last_month_revenue'].iloc[0] * mom_cumprod_se"
   ]
  },
  {
   "cell_type": "raw",
   "id": "5fdaaa5c-b1b3-486e-a8b9-96b7671c26e9",
   "metadata": {},
   "source": [
    "target_month_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "c99973f0-20f7-4870-ad00-dc590e03666d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 构造日期\n",
    "target_month_df['month'] = target_month_df['residual_month_list'].astype(str).apply(lambda x : x if len(x) == 2 else '0'+x)\n",
    "target_month_df['month'] = target_month_df['year'] + '-' + target_month_df['month']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "3ddecc70-39df-48db-afe0-9e3252a7d29c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 拼接前后\n",
    "pred_month_df = target_month_df.loc[:, ['month', 'revenue']]\n",
    "pred_month_df['type'] = 'E'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "91b70e8e-c703-41eb-93b2-747c67935f95",
   "metadata": {},
   "outputs": [],
   "source": [
    "actual_month_df = current_month_df.loc[:, ['month', 'revenue']]\n",
    "actual_month_df['type'] = 'A'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "2b65f0f0-2bbe-49d3-b445-b6800b65c756",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 构造营收预估数据\n",
    "total_month_df = pd.concat([actual_month_df, pred_month_df], axis=0).sort_values(by='month')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "4c243ec7-d2d5-4a0a-8e5d-91b4fe04b090",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>月份</th>\n",
       "      <th>营收（万元）</th>\n",
       "      <th>类型</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>5000.000000</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>5500.000000</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>5200.000000</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-04</td>\n",
       "      <td>5527.385734</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-05</td>\n",
       "      <td>5875.383279</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06</td>\n",
       "      <td>6245.290330</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-07</td>\n",
       "      <td>6638.486283</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-08</td>\n",
       "      <td>7056.437380</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-09</td>\n",
       "      <td>7500.702174</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-10</td>\n",
       "      <td>7972.937344</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-11</td>\n",
       "      <td>8474.903871</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-12</td>\n",
       "      <td>9008.473606</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01</td>\n",
       "      <td>9152.255957</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-02</td>\n",
       "      <td>9298.333187</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-03</td>\n",
       "      <td>9446.741925</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-04</td>\n",
       "      <td>9597.519384</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-05</td>\n",
       "      <td>9750.703370</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-06</td>\n",
       "      <td>9906.332294</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-07</td>\n",
       "      <td>10064.445178</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-08</td>\n",
       "      <td>10225.081668</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-09</td>\n",
       "      <td>10388.282044</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-10</td>\n",
       "      <td>10554.087227</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11</td>\n",
       "      <td>10722.538791</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-12</td>\n",
       "      <td>10893.678975</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-01</td>\n",
       "      <td>11122.260430</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-02</td>\n",
       "      <td>11355.638197</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-03</td>\n",
       "      <td>11593.912917</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-04</td>\n",
       "      <td>11837.187342</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-05</td>\n",
       "      <td>12085.566380</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-06</td>\n",
       "      <td>12339.157142</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-07</td>\n",
       "      <td>12598.068985</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-08</td>\n",
       "      <td>12862.413560</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-09</td>\n",
       "      <td>13132.304863</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-10</td>\n",
       "      <td>13407.859280</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-11</td>\n",
       "      <td>13689.195640</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-12</td>\n",
       "      <td>13976.435264</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        月份        营收（万元） 类型\n",
       "0  2020-01   5000.000000  A\n",
       "1  2020-02   5500.000000  A\n",
       "2  2020-03   5200.000000  A\n",
       "0  2020-04   5527.385734  E\n",
       "0  2020-05   5875.383279  E\n",
       "0  2020-06   6245.290330  E\n",
       "0  2020-07   6638.486283  E\n",
       "0  2020-08   7056.437380  E\n",
       "0  2020-09   7500.702174  E\n",
       "0  2020-10   7972.937344  E\n",
       "0  2020-11   8474.903871  E\n",
       "0  2020-12   9008.473606  E\n",
       "1  2021-01   9152.255957  E\n",
       "1  2021-02   9298.333187  E\n",
       "1  2021-03   9446.741925  E\n",
       "1  2021-04   9597.519384  E\n",
       "1  2021-05   9750.703370  E\n",
       "1  2021-06   9906.332294  E\n",
       "1  2021-07  10064.445178  E\n",
       "1  2021-08  10225.081668  E\n",
       "1  2021-09  10388.282044  E\n",
       "1  2021-10  10554.087227  E\n",
       "1  2021-11  10722.538791  E\n",
       "1  2021-12  10893.678975  E\n",
       "2  2022-01  11122.260430  E\n",
       "2  2022-02  11355.638197  E\n",
       "2  2022-03  11593.912917  E\n",
       "2  2022-04  11837.187342  E\n",
       "2  2022-05  12085.566380  E\n",
       "2  2022-06  12339.157142  E\n",
       "2  2022-07  12598.068985  E\n",
       "2  2022-08  12862.413560  E\n",
       "2  2022-09  13132.304863  E\n",
       "2  2022-10  13407.859280  E\n",
       "2  2022-11  13689.195640  E\n",
       "2  2022-12  13976.435264  E"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_month_df.columns = ['月份', '营收（万元）', '类型']\n",
    "total_month_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "c93a29d1-c1ae-44f8-9491-566289b72d87",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>月份</th>\n",
       "      <th>营收（万元）</th>\n",
       "      <th>类型</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>5000.000000</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>5500.000000</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>5200.000000</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-04</td>\n",
       "      <td>5527.385734</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-05</td>\n",
       "      <td>5875.383279</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        月份       营收（万元） 类型\n",
       "0  2020-01  5000.000000  A\n",
       "1  2020-02  5500.000000  A\n",
       "2  2020-03  5200.000000  A\n",
       "3  2020-04  5527.385734  E\n",
       "4  2020-05  5875.383279  E"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_month_df.index = range(total_month_df.shape[0])\n",
    "total_month_df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8580fce6-45d4-406d-99a9-87f8d713ba41",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 模板导出"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d556fe2f-47dc-474a-8ff4-3f115e1a06d9",
   "metadata": {},
   "source": [
    "官网：https://xlsxwriter.readthedocs.io/  \n",
    "普通案例：https://xlsxwriter.readthedocs.io/examples.html  \n",
    "图表案例：https://xlsxwriter.readthedocs.io/chart_examples.html  \n",
    "样式调整：https://xlsxwriter.readthedocs.io/format.html#format"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "285ca12b-5241-45d3-ba21-db409ecc8af0",
   "metadata": {},
   "source": [
    "### 表格创建"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "a0596f1f-68fc-4cad-9fa1-d1194df61ead",
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlsxwriter as xw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "4b8ee975-14bb-4c9a-a61e-eb97b77203bb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'3.0.1'"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xw.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "7f678395-e195-4246-a565-5189c3b5a71c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建新的excel\n",
    "workbook = xw.Workbook('./营收规划.xlsx')\n",
    "# 添加sheet\n",
    "worksheet = workbook.add_worksheet('每月营收预估')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48e64413-542d-406e-b857-bfd18355032c",
   "metadata": {},
   "source": [
    "### 单元格写入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "d304a4c6-d177-42be-9332-daa2737a7335",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## 一个单元格一个格子去写\n",
    "# 写法1：传统Excel\n",
    "worksheet.write('A1', '月份')\n",
    "worksheet.write('B1', '营收（万元）')\n",
    "# 写法2：行-列索引\n",
    "worksheet.write(0, 2, '类型')  # C1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "id": "cba9082b-05ac-4573-8518-782d05654278",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'CW1'"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数字转字母\n",
    "from xlsxwriter.utility import xl_rowcol_to_cell, xl_cell_to_rowcol\n",
    "xl_rowcol_to_cell(0, 100) # CW1"
   ]
  },
  {
   "cell_type": "raw",
   "id": "9a4f599f-eaac-4b65-b5ba-42701df1a1a6",
   "metadata": {},
   "source": [
    "total_month_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "d07e07e5-8eaa-4030-9bfb-957c131b9dcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 按行写入\n",
    "for row in range(total_month_df.shape[0]):\n",
    "    worksheet.write_row(row+1, 0, total_month_df.iloc[row].values)\n",
    "# 按列写入更好，可以改数据格式：https://xlsxwriter.readthedocs.io/format.html#format"
   ]
  },
  {
   "cell_type": "raw",
   "id": "c4295104-8377-44a2-8f55-00dee4fc3fd7",
   "metadata": {
    "tags": []
   },
   "source": [
    "total_month_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "718c7286-102d-4fd7-938b-240f53b0e6ac",
   "metadata": {},
   "source": [
    "### 公式写入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "45edc16e-3088-4720-a32f-e10a0adb6e1c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## 最下面增加一个合计项目\n",
    "# 获取末尾行\n",
    "last_row = total_month_df.shape[0] + 1 # 最后一行\n",
    "# 格式设置\n",
    "bold = workbook.add_format({'bold': True, 'font_color': '#000000'})\n",
    "worksheet.write(last_row, 0, '合计', bold)\n",
    "# 公式写入\n",
    "worksheet.write(last_row, 1, f'=SUM(B2:B{last_row})', bold)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1295d05b-86e7-4bd7-bd0c-9d7cd85164a2",
   "metadata": {},
   "source": [
    "### 图表写入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "54d0faa6-e64b-47a8-8b59-f69633f9516e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 折线图\n",
    "chart = workbook.add_chart({'type': 'line'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "c3b1631a-9f55-4a10-8615-aa351397188c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chart.add_series({\n",
    "    'categories': f'=每月营收预估!A2:A{last_row}',  # 横坐标\n",
    "    'values': f'=每月营收预估!B2:B{last_row}',  # 数值\n",
    "    'name': '=每月营收预估!B1',  # 列名\n",
    "    'marker': {'type': 'diamond'},  # 增加标记\n",
    "})\n",
    "chart.set_title({'name': '每月营收走势图'})  # 设置标题\n",
    "# 插入表格\n",
    "worksheet.insert_chart('D1', chart)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "a66d5eb8-554b-4a6c-b35e-378f945355af",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 增加框线\n",
    "cell_format = workbook.add_format({'border':1})\n",
    "worksheet.conditional_format('A1:XFD1048576', {'type': 'no_blanks', 'format': cell_format})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "af945004-9173-4b06-9370-6bea0d9bba39",
   "metadata": {},
   "outputs": [],
   "source": [
    "## 保存excel\n",
    "workbook.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65819add-31aa-4c11-8f30-5ce467b96875",
   "metadata": {},
   "source": [
    "## 代码封装"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "61ed4250-1c06-46ee-9711-61666031f471",
   "metadata": {},
   "outputs": [],
   "source": [
    "def predict_month_revenue(current_month_list, target_year_list, file_name='./营收规划.xlsx'):\n",
    "    import numpy as np\n",
    "    import pandas as pd\n",
    "    import xlsxwriter as xw\n",
    "    import numpy_financial as npf\n",
    "    \n",
    "    target_year_dict = {k:v for k,v in target_year_list}\n",
    "    ################################################################\n",
    "    ## 数据读取\n",
    "    # 读取月份数据\n",
    "    current_month_df = pd.DataFrame(current_month_list)\n",
    "    current_month_df.columns = ['month', 'revenue']\n",
    "    current_month_df = current_month_df.sort_values(by='month')\n",
    "    # 生成年份\n",
    "    current_month_df['year'] = current_month_df['month'].apply(lambda x: x[:4])\n",
    "    # 计算当前真实数据中的全年发生，这里计数、金额合计都需要\n",
    "    current_group_df = current_month_df.groupby('year').agg({'month': 'count', 'revenue': 'sum'})\n",
    "\n",
    "    ################################################################\n",
    "    ## 增速计算准备\n",
    "    # 当前年份\n",
    "    this_year = current_group_df.index[0]\n",
    "    # 获取当前年份的目标\n",
    "    this_year_target = target_year_dict[this_year]\n",
    "    # 当年剩余营收目标\n",
    "    this_year_residual = this_year_target - current_group_df['revenue'][0]\n",
    "    # 当前剩余月份\n",
    "    this_year_month_past = current_group_df['month'][0]\n",
    "    this_year_month_residual = 12 - this_year_month_past\n",
    "    # 假设增速年内每月固定增长r，真实的末月为x，预估的首月为x*(1+r)，次月为x*(1+r)**2... 累计所有和全年剩余目标接近即可\n",
    "    this_year_actual_last = current_month_df[current_month_df['year'] == this_year]['revenue'].iloc[-1]\n",
    "\n",
    "    ################################################################\n",
    "    # 目标年份构建\n",
    "    target_year_df = pd.DataFrame(target_year_list)\n",
    "    target_year_df.columns = ['year', 'revenue']\n",
    "    target_year_df = target_year_df.sort_values(by='year')\n",
    "    target_year_df['completed'] = 0\n",
    "    target_year_df['residual_month'] = 12\n",
    "    target_year_df['last_month_revenue'] = 0\n",
    "\n",
    "    # 给已完成的年份赋值\n",
    "    target_year_df = target_year_df.set_index('year')\n",
    "    target_year_df.loc[this_year, 'completed'] = this_year_target - this_year_residual\n",
    "    target_year_df.loc[this_year, 'residual_month'] = this_year_month_residual\n",
    "    target_year_df.loc[this_year, 'last_month_revenue'] = this_year_actual_last\n",
    "    target_year_df = target_year_df.reset_index()\n",
    "\n",
    "    # 增速计算\n",
    "    every_irr_list = []  # 存储每年计算月增速\n",
    "    last_month_revenue_list = []  # 存储每年末月收入\n",
    "    for i in target_year_df.index:\n",
    "        # 取数\n",
    "        tmp_revenue = target_year_df.loc[i, 'revenue'] \n",
    "        tmp_completed = target_year_df.loc[i, 'completed'] \n",
    "        tmp_residual_month = target_year_df.loc[i, 'residual_month'] \n",
    "        tmp_last_month_revenue = target_year_df.loc[i, 'last_month_revenue'] \n",
    "        # 获取真实上年最末月的值\n",
    "        if not last_month_revenue_list:  # 如果没存的话，就用df的\n",
    "            last_month_revenue_list.append(tmp_last_month_revenue)\n",
    "        tmp_last_month_revenue = last_month_revenue_list[-1]\n",
    "        # 计算irr\n",
    "        tmp_irr_in_list = [tmp_last_month_revenue for _ in range(tmp_residual_month)]\n",
    "        tmp_irr_total_list = [-(tmp_revenue - tmp_completed)] + tmp_irr_in_list\n",
    "        tmp_irr = npf.irr(tmp_irr_total_list)\n",
    "        tmp_irr2rate = 1 / (1 + tmp_irr) - 1 \n",
    "        every_irr_list.append(tmp_irr2rate)\n",
    "        # 给末月赋值用于下次迭代\n",
    "        iter_last_revenue = tmp_last_month_revenue * (1+tmp_irr2rate) ** (tmp_residual_month)\n",
    "        last_month_revenue_list.append(iter_last_revenue)  # 再存一次\n",
    "\n",
    "    target_year_df['mom'] = every_irr_list\n",
    "    target_year_df['last_month_revenue'] = last_month_revenue_list[:-1]\n",
    "    \n",
    "    ################################################################\n",
    "    ## 对年度数据炸裂到月度\n",
    "    target_year_df['residual_month_list'] = target_year_df['residual_month'].apply(lambda x : [12-x+i+1 for i in range(x)])\n",
    "    target_month_df = target_year_df.explode('residual_month_list')\n",
    "    # 使用累乘的方法\n",
    "    mom_cumprod_se = (target_month_df['mom'] + 1).cumprod()\n",
    "    target_month_df['revenue'] = target_month_df['last_month_revenue'].iloc[0] * mom_cumprod_se\n",
    "    # 构造日期\n",
    "    target_month_df['month'] = target_month_df['residual_month_list'].astype(str).apply(lambda x : x if len(x) == 2 else '0'+x)\n",
    "    target_month_df['month'] = target_month_df['year'] + '-' + target_month_df['month']\n",
    "    \n",
    "    ################################################################\n",
    "    ## 数据拼接导出\n",
    "    pred_month_df = target_month_df.loc[:, ['month', 'revenue']]\n",
    "    pred_month_df['type'] = 'E'\n",
    "    actual_month_df = current_month_df.loc[:, ['month', 'revenue']]\n",
    "    actual_month_df['type'] = 'A'\n",
    "    total_month_df = pd.concat([actual_month_df, pred_month_df], axis=0).sort_values(by='month')\n",
    "    total_month_df.columns = ['月份', '营收（万元）', '类型']\n",
    "    total_month_df.index = range(total_month_df.shape[0])\n",
    "    \n",
    "    ################################################################\n",
    "    ## 导出excel\n",
    "    workbook = xw.Workbook(file_name)\n",
    "    # 添加sheet\n",
    "    worksheet = workbook.add_worksheet('每月营收预估')\n",
    "    ## 一个单元格一个格子去写\n",
    "    # 写法1：传统Excel\n",
    "    worksheet.write('A1', '月份')\n",
    "    worksheet.write('B1', '营收（万元）')\n",
    "    # 写法2：行-列索引\n",
    "    worksheet.write(0, 2, '类型')  # C1\n",
    "    # 按行写入\n",
    "    for row in range(total_month_df.shape[0]):\n",
    "        worksheet.write_row(row+1, 0, total_month_df.iloc[row].values)\n",
    "\n",
    "    ## 最下面增加一个合计项目\n",
    "    # 获取末尾行\n",
    "    last_row = total_month_df.shape[0] + 1# 最后一行\n",
    "    # 格式设置\n",
    "    bold = workbook.add_format({'bold': True, 'font_color': '#000000'})\n",
    "    worksheet.write(last_row, 0, '合计', bold)\n",
    "    # 公式写入\n",
    "    worksheet.write(last_row, 1, f'=SUM(B2:B{last_row})', bold)\n",
    "\n",
    "    ## 折线图\n",
    "    chart = workbook.add_chart({'type': 'line'})\n",
    "    chart.add_series({\n",
    "        'categories': f'=每月营收预估!A2:A{last_row}',  # 横坐标\n",
    "        'values': f'=每月营收预估!B2:B{last_row}',  # 数值\n",
    "        'name': '=每月营收预估!B1',  # 列名\n",
    "        'marker': {'type': 'diamond'},  # 增加标记\n",
    "    })\n",
    "    chart.set_title({'name': '每月营收走势图'})  # 设置标题\n",
    "    # 插入表格\n",
    "    worksheet.insert_chart('D1', chart)\n",
    "    # 增加框线\n",
    "    cell_format = workbook.add_format({'border':1})\n",
    "    worksheet.conditional_format('A1:XFD1048576', {'type': 'no_blanks', 'format': cell_format})\n",
    "    ## 保存excel\n",
    "    workbook.close()\n",
    "    print('预估表格生成成功！')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "c8153916-f083-4fa5-b798-839361926392",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 当前每月营收\n",
    "current_month_list = [\n",
    "    ['2020-01', 5000],\n",
    "    ['2020-02', 5500],\n",
    "    ['2020-03', 5200],\n",
    "    ['2020-04', 5000],\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "58764df2-5ffd-4674-8f4e-8e6577de9d6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 目标全年营收\n",
    "target_year_list = [\n",
    "    ['2020', 80000],\n",
    "    ['2021', 120000],\n",
    "    ['2022', 180000],\n",
    "    ['2023', 300000],\n",
    "]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "07e5f03d-6cc9-4ce2-8668-673efd800d1a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "预估表格生成成功！\n"
     ]
    }
   ],
   "source": [
    "predict_month_revenue(current_month_list, target_year_list, '新.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa889341-f585-460e-884b-4433ecfd6b5f",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
